CREATE SEQUENCE loc_sequence
START WITH 1
INCREMENT BY 1;

CREATE TRIGGER loc_trigger
BEFORE INSERT ON LOCATION
FOR EACH ROW
BEGIN
    SELECT loc_sequence.nextval into :new.LOC_ID from dual;
END;
create table location(
    loc_id  number,
    city char(30),
    state char(30),
    country char(30),
    primary key (loc_id));

create table photos(
    photo_id number,
    album_id number,
    photo_caption char(100),
    photo_link char(30) NOT NULL,
    modified_time time,
    created_time time,
    primary key (photo_id),
    foreign key (album_id) REFERENCES album
);

create table album(
    album_id number,
    name char(30) NOT NULL,
    created_time time,
    modified_time time,
    link char(30) NOT NULL,
    visibility char(30) NOT NULL,
    cover_photo_id number NOT NULL,
    primary key (album_id)
    foreign key (cover_photo_id) REFERENCES photos);

create table users(
    user_id number,
    gender char(8),
    day_of_birth char(12),
    month_of_birth char(12),
    year_of_birth number,
    first_name char(30) NOT NULL,
    last_name char(30) NOT NULL,
    home_loc number,
    current_loc number,
    primary key(user_id),
    FOREIGN KEY (home_loc) REFERENCES location,
    FOREIGN KEY (current_loc) REFERENCES location);

create table event(
    event_id number,
    name char(30) NOT NULL,
    tagline char(30),
    description char(200),
    start_time time,
    end_time time,
    event_type char(30),
    event_subtype char(30),
    loc_id number,
    event_creator_id number,
    event_host number,
    primary key (event_id),
    FOREIGN KEY (loc_id) REFERENCES location,
    FOREIGN KEY (event_creator_id) REFERENCES users,
    FOREIGN KEY (event_host) REFERENCES users);

create table friends(
    user_id1 number,
    user_id2 number;
    primary key (user_id1, user_id2));
